<?php
/**
 * Created by PhpStorm.
 * User: mr_lee
 * Date: 2017/11/1
 * Time: 下午6:45
 */

namespace app\common\command;

use app\common\model\CardStatis;
use app\common\model\MchStatis;
use app\common\model\Merchant;
use app\common\model\Mstatis;
use app\common\model\Paylog;
use app\common\model\Statis;
use app\common\model\Tklog;
use think\console\Command;
use think\console\Input;
use think\console\Output;

class Tstatis extends Command
{
    protected function configure()
    {
        $this->setName('tstatis')->setDescription('Here is the remark ');
    }

    protected function execute(Input $input, Output $output)
    {
        $this->mch_pay_statis();
        $this->cardstatis();
//        $this->machday();
        $output->writeln("统计成功:");
    }

    private function mch_pay_statis()
    {
        $MchStatis = new MchStatis();
        $infos2 = [];
        $wxpay = [];
        $alipay = [];
        $cashpay = [];
        $wxcardpay = [];
        $othercount = [];
        $other2 = [];
        $start_time = strtotime(date('Y-m-d')) - 86400;
        $end_time = $start_time + 86399;
        $statisinfo = $MchStatis->where('statis_times', $start_time)->find();
        if (!$statisinfo) {
            $condition = "p.mid = a.id and  pay_cate <> 'recharge' and p.pay_statu <> 0 and p.pay_statu <> 8 and (p.add_time >= :start_time and p.add_time <= :end_time)";
            $param = ['start_time' => $start_time, 'end_time' => $end_time];
            //微信支付统计 带手续费
            $condition_1 = $condition . "  and p.pay_fs in (0,2)";
            $field = 'a.id as mid,a.top_mid,a.agent,sum(p.price) as wx_fee,count(p.id) as wx_order,sum(p.service_charge) as wx_service_charge';
            $wxpay = $this->statissql($condition_1, $param, $field);

            //支付宝支付统计 带手续费
            $condition_2 = $condition . "  and p.pay_fs in (1,3)";
            $field = 'a.id as mid,a.top_mid,a.agent,sum(p.price) as ali_fee,count(p.id) as ali_order,sum(p.service_charge) as ali_service_charge';
            $alipay = $this->statissql($condition_2, $param, $field);
            //POS支付统计 带手续费
            $condition_6 = $condition . "  and p.pay_fs = 5";
            $field = 'a.id as mid,a.top_mid,a.agent,sum(p.price) as pos_fee,count(p.id) as pos_order,sum(p.service_charge) as pos_service_charge';
            $pospay = $this->statissql($condition_6, $param, $field);
            //现金支付统计
            $condition_3 = $condition . "  and p.pay_fs = 4";
            $field = 'a.id as mid,a.top_mid,a.agent,sum(p.price) as cash_fee,count(p.id) as cash_order';

            $cashpay = $this->statissql($condition_3, $param, $field);
            //会员卡支付统计
            $condition_4 = $condition . "  and p.pay_fs = 8";
            $field = 'a.id as mid,a.top_mid,a.agent,sum(p.price) as wx_card_fee,count(p.id) as wx_card_order';
            $wxcardpay = $this->statissql($condition_4, $param, $field);
            //折扣 应收 实收统计
            $condition_o = $condition . " and p.pay_fs in (0,1,2,3,4,8)";
            $field = 'a.id as mid,sum(p.zk_price) as dis_fee,a.top_mid,a.agent,sum(p.yf_price) as ought_fee,sum(p.price) as real_fee,count(p.id) as real_order,avg(p.price) as avg_fee';
            $othercount = $this->statissql($condition_o, $param, $field);
            //退款统计
            $Merchant = new Merchant();
            $other2 = $Merchant
                ->alias('a')
                ->join('wn_tklog t', 't.mid = a.id and t.tk_status = 1 and (t.addtime >= :start_time and t.addtime <= :end_time)', 'left')
                ->group('a.id')
                ->bind($param)
                ->field('a.id as mid,a.top_mid,a.agent,sum(t.refund_fee) as refund_fee,count(t.id) as refund_order')
                ->select();
            $infos = array_merge($wxpay, $alipay,$pospay, $cashpay, $wxcardpay, $othercount, $other2);
            $mchlist = array_unique(array_column($wxpay, 'mid'));
            foreach ($mchlist as $k => $v) {
                foreach ($infos as $ks => $vs) {
                    if ($vs['mid'] == $v) {
                        $infos2[$k]['mid'] = $vs['mid'];
                        $infos2[$k]['agent'] = $vs['agent'];
                        $infos2[$k]['top_mid'] = $vs['top_mid'];
                        $infos2[$k]['create_time'] = time();
                        $infos2[$k]['statis_time'] = date('Y-m-d H:i:s', $start_time);
                        $infos2[$k]['statis_times'] = $start_time;
                        $infos2[$k]['statis_action'] = 1;
                        if (isset($vs['wx_order'])) {
                            $infos2[$k]['wx_fee'] = !empty($vs['wx_fee']) ? $vs['wx_fee'] : 0.00;
                            $infos2[$k]['wx_order'] = $vs['wx_order'];
                            $infos2[$k]['wx_service_charge'] = !empty($vs['wx_service_charge']) ? $vs['wx_service_charge'] : 0.00;
                        }
                        if (isset($vs['real_order'])) {
                            $infos2[$k]['dis_fee'] = !empty($vs['dis_fee']) ? $vs['dis_fee'] : 0.00;
                            $infos2[$k]['ought_fee'] = !empty($vs['ought_fee']) ? $vs['ought_fee'] : 0.00;
                            $infos2[$k]['avg_fee'] = !empty($vs['avg_fee']) ? $vs['avg_fee'] : 0.00;
                            $infos2[$k]['real_fee'] = !empty($vs['real_fee']) ? $vs['real_fee'] : 0.00;
                            $infos2[$k]['real_order'] = !empty($vs['real_order']) ? $vs['real_order'] : 0;
                        }
                        if (isset($vs['ali_order'])) {
                            $infos2[$k]['ali_fee'] = !empty($vs['ali_fee']) ? $vs['ali_fee'] : 0.00;
                            $infos2[$k]['ali_order'] = $vs['ali_order'];
                            $infos2[$k]['ali_service_charge'] = !empty($vs['ali_service_charge']) ? $vs['ali_service_charge'] : 0.00;
                        }
                        if (isset($vs['pos_order'])) {
                            $infos2[$k]['pos_fee'] = !empty($vs['pos_fee']) ? $vs['pos_fee'] : 0.00;
                            $infos2[$k]['pos_order'] = $vs['pos_order'];
                            $infos2[$k]['pos_service_charge'] = !empty($vs['pos_service_charge']) ? $vs['pos_service_charge'] : 0.00;
                        }
                        if (isset($vs['cash_order'])) {
                            $infos2[$k]['cash_fee'] = !empty($vs['cash_fee']) ? $vs['cash_fee'] : 0.00;
                            $infos2[$k]['cash_order'] = $vs['cash_order'];
                        }
                        if (isset($vs['wx_card_order'])) {
                            $infos2[$k]['wx_card_fee'] = !empty($vs['wx_card_fee']) ? $vs['wx_card_fee'] : 0.00;
                            $infos2[$k]['wx_card_order'] = $vs['wx_card_order'];
                        }
                        if (isset($vs['refund_order'])) {
                            $infos2[$k]['refund_fee'] = !empty($vs['refund_fee']) ? $vs['refund_fee'] : 0.00;
                            $infos2[$k]['refund_order'] = $vs['refund_order'];
                        }
                    }
                }
            }
            $MchStatis = new MchStatis();
            $MchStatis->saveAll($infos2);
        }

    }

    private function statissql($condition, $param, $field)
    {
        $Merchant = new Merchant();
        $sql = $Merchant
            ->alias('a')
            ->join('wn_paylog p', $condition, "LEFT")
            ->group('a.id')
            ->bind($param)
            ->field($field)
            ->select();

//        return $sql;

        return collection($sql)->toArray();

    }

    public function day()
    {
        $paylog = new Paylog();
        $Statis = new Statis();
        $day = strtotime(date('Y-m-d', strtotime('-1 day')));
        $day2 = strtotime(date('Y-m-d')) - 1;
        $map['add_time'] = array('between', '' . $day . ',' . $day2 . '');
        $map['pay_statu'] = array(array('neq', 0), array('neq', 8), 'and');
        $jorder = $paylog->where($map)->count();
        $mae = $map;
        $mae['tk_price'] = array('neq', 0.00);
        $torder = $paylog->where($mae)->count();
        $data['orders'] = @($jorder - $torder);
        $jprice = $paylog->where($map)->sum('price');
        $tprice = $paylog->where($map)->sum('tk_price');
        $data['price'] = @($jprice - $tprice);
        $data['tk_price'] = $tprice;
        $data['tk_order'] = $torder;
        $data['mach'] = $paylog->where($map)->count('distinct(mid)');
        $data['dtime'] = $day;
        $data['addtime'] = date('Y-m-d H:i:s');
        $data['fs'] = 'day';
        $where['dtime'] = $day;
        $where['fs'] = 'day';
        $cx = $Statis->where($where)->count();
        if ($cx > 0) {
            $Statis->save($data, $where);
        } else {
            $Statis->save($data);
        }
    }

    public function mch_statis()
    {
        $Merchant = new Merchant();
        $machlist = $Merchant->field('id,merchant,top_mid,agent')->order('id asc')->select();
        $MchStatis = new MchStatis();
        $Paylog = new Paylog();
        foreach ($machlist as $k => $v) {
            $day = strtotime(date('Y-m-d', strtotime('-1 day')));
            $day2 = $day + 86399;
            $where['statis_times'] = $day;
            $where['mid'] = $v['id'];
            $cx = $MchStatis->where($where)->count();
            if ($cx == 0) {
                $map['mid'] = $v['id'];
                $map['add_time'] = array('between', '' . $day . ',' . $day2 . '');
                $map['pay_statu'] = array(array('neq', 0), array('neq', 8), 'and');
                $jorder = $Paylog->where($map)->count();
                $mae = $map;
                $mae['tk_price'] = array('neq', 0.00);
                $torder = $Paylog->where($mae)->count();
                $data[$k]['orders'] = $jorder - $torder;
                $jprice = $Paylog->where($map)->sum('price');
                $tprice = $Paylog->where($map)->sum('tk_price');
                $price = @($jprice - $tprice);
                $data[$k]['price'] = isset($price) ? $price : 0;
                $data[$k]['mach'] = $Paylog->where($map)->count('distinct(mid)');
                $data[$k]['mid'] = $v['id'];
                $data[$k]['merchant'] = $v['merchant'];
                $data[$k]['dtime'] = $day;
                $data[$k]['addtime'] = date('Y-m-d H:i:s', $day2);
                $data[$k]['tk_price'] = $tprice;
                $data[$k]['tk_order'] = $torder;
                $data[$k]['fs'] = 'day';
            }
        }
    }

    //每日商户统计
    public function machday()
    {
        $paylog = new Paylog();
        $Merchant = new Merchant();
        $Mstatis = new Mstatis();
        $machlist = $Merchant->field('id,merchant,addtime')->order('id asc')->select();
        $data = [];
        foreach ($machlist as $k => $v) {
            $day = strtotime(date('Y-m-d', strtotime('-1 day')));
            $day2 = $day + 86399;
            $where['dtime'] = $day;
            $where['mid'] = $v['id'];
            $where['fs'] = 'day';
            $cx = $Mstatis->where($where)->count();
            if ($cx == 0) {
                $map['mid'] = $v['id'];
                $map['add_time'] = array('between', '' . $day . ',' . $day2 . '');
                $map['pay_statu'] = array(array('neq', 0), array('neq', 8), 'and');
                $jorder = $paylog->where($map)->count();
                $mae = $map;
                $mae['tk_price'] = array('neq', 0.00);
                $torder = $paylog->where($mae)->count();
                $data[$k]['orders'] = $jorder - $torder;
                $jprice = $paylog->where($map)->sum('price');
                $tprice = $paylog->where($map)->sum('tk_price');
                $price = @($jprice - $tprice);
                $data[$k]['price'] = isset($price) ? $price : 0;
                $data[$k]['mach'] = $paylog->where($map)->count('distinct(mid)');
                $data[$k]['mid'] = $v['id'];
                $data[$k]['merchant'] = $v['merchant'];
                $data[$k]['dtime'] = $day;
                $data[$k]['addtime'] = date('Y-m-d H:i:s', $day2);
                $data[$k]['tk_price'] = $tprice;
                $data[$k]['tk_order'] = $torder;
                $data[$k]['fs'] = 'day';
            }
        }
        $Mstatis->allowField(true)->saveAll($data);
    }

    public function cardstatis()
    {
        //时间
        $start_time = strtotime(date('Y-m-d',strtotime('-1 day')));
        $end_time = strtotime(date('Y-m-d')) - 1;
        //领卡统计
        $condition_1 = "b.mid = a.id and b.card_type='MEMBER_CARD' and (b.create_time >= :start_time and b.create_time <= :end_time)";
        $param = ['start_time' => $start_time, 'end_time' => $end_time];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,count(b.id) as rec_card_num';
        $rec_card_data = $this->getcardstatis($condition_1, $param, $field);
        //退卡统计
        $condition_2 = "b.mid = a.id and b.card_type='MEMBER_CARD' and have =0 and (b.del_time >= :start_time and b.del_time <= :end_time)";
        $param = ['start_time' => $start_time, 'end_time' => $end_time];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,count(b.id) as ret_card_num';
        $ret_card_data = $this->getcardstatis($condition_2, $param, $field);
//        //实充消费 赠送统计
        $condition = "b.mid = a.id and cate=:cate and pay_cate=:pay_cate and type=1 and (b.create_time >= :start_time and b.create_time <= :end_time)";
        //微信实充金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 1, 'pay_cate' => 2];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as wx_rec_fee';
        $wx_rec_data = $this->getcardfeestatis($condition, $param, $field);
        //微信赠送金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 3, 'pay_cate' => 2];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as wx_give_fee';
        $wx_give_data = $this->getcardfeestatis($condition, $param, $field);
        //微信自助实充金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 1, 'pay_cate' => 0];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as wxonline_rec_fee';
        $wxonline_rec_data = $this->getcardfeestatis($condition, $param, $field);
        //微信自助赠送金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 3, 'pay_cate' => 0];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as wxonline_give_fee';
        $wxonline_give_data = $this->getcardfeestatis($condition, $param, $field);
        //支付宝实充金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 1, 'pay_cate' => 1];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as ali_rec_fee';
        $ali_rec_data = $this->getcardfeestatis($condition, $param, $field);
        //支付宝赠送
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 3, 'pay_cate' => 1];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as ali_give_fee';
        $ali_give_data = $this->getcardfeestatis($condition, $param, $field);
        //支付宝自助充值金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 1, 'pay_cate' => 3];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as alionline_rec_fee';
        $alionline_rec_data = $this->getcardfeestatis($condition, $param, $field);
        //支付宝自助赠送
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 3, 'pay_cate' => 3];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as alionline_give_fee';
        $alionline_give_data = $this->getcardfeestatis($condition, $param, $field);
        //现金实充金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 1, 'pay_cate' => 4];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as cash_rec_fee';
        $cash_rec_data = $this->getcardfeestatis($condition, $param, $field);
        //现金充值赠送金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 3, 'pay_cate' => 4];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as cash_give_fee';
        $cash_give_data = $this->getcardfeestatis($condition, $param, $field);
        $condition_z = "b.mid = a.id and cate=:cate and pay_cate in (0,1,2,3,4) and type=1 and (b.create_time >= :start_time and b.create_time <= :end_time)";
        //总计充值金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 1];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as rec_fee';
        $rec_data = $this->getcardfeestatis($condition_z, $param, $field);
        //总计赠送金额
        $param = ['start_time' => $start_time, 'end_time' => $end_time, 'cate' => 3];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as give_fee';
        $give_data = $this->getcardfeestatis($condition_z, $param, $field);
        //会员卡消费金额&次数
        $condition_con = "b.mid = a.id and type=2 and cate=2 and (b.create_time >= :start_time and b.create_time <= :end_time)";
        $param = ['start_time' => $start_time, 'end_time' => $end_time];
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as con_fee,count(b.id) as con_order';
        $con_data = $this->getcardfeestatis($condition_con, $param, $field);
        //会员卡赠送积分
        $condition_bonus = "b.mid = a.id and type=1 and cate not in(2,9) and (b.create_time >= :start_time and b.create_time <= :end_time)";
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as give_bonus';
        $param = ['start_time' => $start_time, 'end_time' => $end_time];
        $rec_bonus_data = $this->getcardbonusstatis($condition_bonus, $param, $field);
        $condition_bonus = "b.mid = a.id and type=2 and cate in(2,9) and (b.create_time >= :start_time and b.create_time <= :end_time)";
        $field = 'a.id as mid,a.top_mid,a.agent,a.id,sum(b.num) as con_bonus';
        $param = ['start_time' => $start_time, 'end_time' => $end_time];
        $con_bonus_data = $this->getcardbonusstatis($condition_bonus, $param, $field);
        $infos = array_merge($rec_card_data, $ret_card_data, $wx_rec_data, $wx_give_data, $wxonline_rec_data, $wxonline_give_data,
            $ali_rec_data, $ali_give_data, $alionline_rec_data, $alionline_give_data,
            $cash_rec_data, $cash_give_data, $rec_data, $give_data, $con_data, $rec_bonus_data, $con_bonus_data
        );
        $mchlist = array_unique(array_column($rec_data, 'mid'));
        foreach ($mchlist as $k => $v) {
            foreach ($infos as $ks => $vs) {
                if ($vs['mid'] == $v) {
                    $infos2[$k]['mid'] = $vs['mid'];
                    $infos2[$k]['agent'] = $vs['agent'];
                    $infos2[$k]['top_mid'] = $vs['top_mid'];
                    $infos2[$k]['create_time'] = time();
                    $infos2[$k]['statis_time'] = date('Y-m-d H:i:s', $start_time);
                    $infos2[$k]['statis_times'] = $start_time;
                    $infos2[$k]['statis_action'] = 1;
                    $infos2[$k]['month'] = date('Y-m', $start_time);
                    if (array_key_exists('rec_card_num', $vs)) {
                        $infos2[$k]['rec_card_num'] = number_format($vs['rec_card_num'],2);
                    }
                    if (array_key_exists('ret_card_num', $vs)) {
                        $infos2[$k]['ret_card_num'] = number_format($vs['ret_card_num'],2);
                    }
                    if (array_key_exists('wx_rec_fee', $vs)) {
                        $infos2[$k]['wx_rec_fee'] = number_format($vs['wx_rec_fee'],2);
                    }
                    if (array_key_exists('wx_give_fee', $vs)) {
                        $infos2[$k]['wx_give_fee'] = number_format($vs['wx_give_fee'],2);
                    }
                    if (array_key_exists('wxonline_rec_fee', $vs)) {
                        $infos2[$k]['wxonline_rec_fee'] = number_format($vs['wxonline_rec_fee'],2);
                    }
                    if (array_key_exists('wxonline_give_fee', $vs)) {
                        $infos2[$k]['wxonline_give_fee'] = number_format($vs['wxonline_give_fee'],2);
                    }
                    if (array_key_exists('ali_rec_fee', $vs)) {
                        $infos2[$k]['ali_rec_fee'] = number_format($vs['ali_rec_fee'],2);
                    }
                    if (array_key_exists('ali_give_fee', $vs)) {
                        $infos2[$k]['ali_give_fee'] = number_format($vs['ali_give_fee'],2);
                    }
                    if (array_key_exists('alionline_give_fee', $vs)) {
                        $infos2[$k]['alionline_give_fee'] = number_format($vs['alionline_give_fee'],2);
                    }
                    if (array_key_exists('alionline_rec_fee', $vs)) {
                        $infos2[$k]['alionline_rec_fee'] = number_format($vs['alionline_rec_fee'],2);
                    }
                    if (array_key_exists('cash_rec_fee', $vs)) {
                        $infos2[$k]['cash_rec_fee'] = number_format($vs['cash_rec_fee'],2);
                    }
                    if (array_key_exists('cash_give_fee', $vs)) {
                        $infos2[$k]['cash_give_fee'] = number_format($vs['cash_give_fee'],2);
                    }
                    if (array_key_exists('rec_fee', $vs)) {
                        $infos2[$k]['rec_fee'] = number_format($vs['rec_fee'],2);
                    }
                    if (array_key_exists('give_fee', $vs)) {
                        $infos2[$k]['give_fee'] = number_format($vs['give_fee'],2);
                    }
                    if (array_key_exists('con_fee', $vs)) {
                        $infos2[$k]['con_fee'] = number_format($vs['con_fee'],2);
                    }
                    if (array_key_exists('give_bonus', $vs)) {
                        $infos2[$k]['give_bonus'] = $vs['give_bonus'];
                    }
                    if (array_key_exists('con_order', $vs)) {
                        $infos2[$k]['con_order'] = $vs['con_order'];
                    }
                    if (array_key_exists('con_bonus', $vs)) {
                        $infos2[$k]['con_bonus'] = $vs['con_bonus'];
                    }
                }
            }
        }
        $MchStatis = new CardStatis();
        $MchStatis->saveAll($infos2);

        var_dump($infos2);
    }

    private function getcardstatis($condition, $param, $field)
    {
        $Merchant = new Merchant();
        $sql = $Merchant
            ->alias('a')
            ->join('wn_card_user_get b', $condition, "LEFT")
            ->group('a.id')
            ->bind($param)
            ->field($field)
            ->select();
        return collection($sql)->toArray();

    }

    private function getcardfeestatis($condition, $param, $field)
    {
        $Merchant = new Merchant();
        $sql = $Merchant
            ->alias('a')
            ->join('wn_card_user_fee b', $condition, "LEFT")
            ->group('a.id')
            ->bind($param)
            ->field($field)
            ->select();
        return collection($sql)->toArray();
    }

    private function getcardbonusstatis($condition, $param, $field)
    {
        $Merchant = new Merchant();

        $sql = $Merchant
            ->alias('a')
            ->join('wn_card_user_bonus b', $condition, "LEFT")
            ->group('a.id')
            ->bind($param)
            ->field($field)
            ->select();
        return collection($sql)->toArray();

    }

}
